{*******************************************************************************
* The contents of this file are used with permission, subject to the Mozilla   *
* Public License Version 1.1 (the "License"); you may not use this file except *
* in compliance with the License. You may obtain a copy of the License at      *
* http://www.mozilla.org/MPL/                                                  *
*                                                                              *
* Software distributed under the License is distributed on an "AS IS" basis,   *
* WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for *
* the specific language governing rights and limitations under the License.    *
*                                                                              *
* The Original Code is distributed as part of the "mediate" product and is     *
* Copyright (C) @home media limited. All Rights Reserved.                      *
*                                                                              *
* Email: support@athomemedia.co.uk                                             *
* Web:   http://www.athomemedia.co.uk                                          *
*******************************************************************************}

unit DBSchema;

interface

uses SQLiteTable3, SQLite3;

const
  DB_ANY_VERSION = -1;

type
  TAHMDBResult = TSQLiteTable;
  TAHMDBQuery = TSQLiteQuery;

  TAHMDBSchema = class
  private
    FMinVersion: Integer;
    FFileName: String;
  protected
    FDB: TSQLiteDatabase;
    procedure SetFileName(FileName: String);
    function GetSchemaVersion: Integer;
    procedure SetSchemaVersion(Version: Integer);
    function GetTableVersion(TableName: String): Integer;
  public
    constructor Create;
    destructor Destroy; override;
    property Version: Integer read GetSchemaVersion;
    property MinVersion: Integer read FMinVersion write FMinVersion;
    property DB: TSQLiteDatabase read FDB;
    property FileName: String read FFileName write SetFileName;
    function CheckSchema: Boolean; virtual;
    procedure Close;
  end;


implementation

uses SysUtils;

const
  // Version management
  TBL_VERSIONINFO = 'VersionInfo';
  SQL_GET_SCHEMA_VERSION_NUMBER = 'SELECT VersionNo FROM VersionInfo ' +
                                  'WHERE Item="Schema"';
  SQL_GET_TABLE_VERSION_NUMBER  = 'SELECT VersionNo FROM VersionInfo ' +
                                  'WHERE Item="%0:s"';
  SQL_BUILD_VERSION_TABLE       = 'CREATE TABLE VersionInfo '+
                                  '([Item] CHAR PRIMARY KEY,'+
                                  '[VersionNo] INTEGER)';
  SQL_POPULATE_VERSION_TABLE    = 'INSERT INTO VersionInfo(Item, VersionNo) ' +
                                  'VALUES("Schema", 0)';
  SQL_UPDATE_SCHEMA_VERSION     = 'UPDATE VersionInfo ' +
                                  'SET VersionNo=%0:d ' +
                                  'WHERE Item="Schema"';

// TAHMDBSchema

constructor TAHMDBSchema.Create;
begin
  inherited;

  // Set default required schema version
  FMinVersion := DB_ANY_VERSION;
end;

destructor TAHMDBSchema.Destroy;
begin
  Close;

  inherited;
end;

procedure TAHMDBSchema.SetFileName(FileName: String);
begin
  // Is this a different database?
  if FileName <> FFileName then
  begin
    // Close current DB
    Close;

    // Create a new instance and record location
    FDB := TSQLiteDatabase.Create(FileName);
    FFileName := FileName;
  end;
end;


function TAHMDBSchema.GetSchemaVersion: Integer;
begin
  Result := DB_ANY_VERSION;

  if Assigned(FDB) then
  begin
    if FDB.TableExists(TBL_VERSIONINFO) then
      Result := FDB.GetTableValue(SQL_GET_SCHEMA_VERSION_NUMBER)
    else
    begin
      FDB.ExecSQL(SQL_BUILD_VERSION_TABLE);
      FDB.ExecSQL(SQL_POPULATE_VERSION_TABLE);
    end;
  end;
end;

procedure TAHMDBSchema.SetSchemaVersion(Version: Integer);
begin
  if Assigned(FDB) then
  begin
    // Create and seed table if it doesn't yet exist
    if not FDB.TableExists(TBL_VERSIONINFO) then
    begin
      FDB.ExecSQL(SQL_BUILD_VERSION_TABLE);
      FDB.ExecSQL(SQL_POPULATE_VERSION_TABLE);
    end;

    // Update version number
    FDB.ExecSQL(Format(SQL_UPDATE_SCHEMA_VERSION, [Version]));
  end;
end;

function TAHMDBSchema.GetTableVersion(TableName: String): Integer;
begin
  Result := 0;

  if Assigned(FDB) then
    if FDB.TableExists(TBL_VERSIONINFO) then
      Result := FDB.GetTableValue(Format(SQL_GET_TABLE_VERSION_NUMBER, [TableName]));
end;

function TAHMDBSchema.CheckSchema: Boolean;
begin
  // Descendants should implement this, for example by checking that the
  // schema version exceeds the minimum version number required (FMinVersion)
  Result := False;
end;

procedure TAHMDBSchema.Close;
begin
  // Release our database instance
  FreeAndNil(FDB);
end;



end.
